﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Customers : System.Web.UI.Page
{
    SqlConnection con;
    SqlDataAdapter da;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.IsPostBack == false)
        {
                FillCustomers();
                ViewState["istext"] = -1;
                ViewState["isSearch"] = false;
                ViewState["Sort"] = "ASC";
                ViewState["gv"] = gvCustomers.DataSource;
        }
        
    }
    void FillCustomers()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            da = new SqlDataAdapter("getCustomers", con);
            dt = new DataTable("Sales.Customers");
            da.Fill(dt);
            gvCustomers.AutoGenerateSelectButton = true;
            gvCustomers.AllowPaging = true;
            gvCustomers.PageSize = 10;
            //DataView dv = new DataView(dt);
            //dv.Sort = "contactname DESC";
            //dt = dv.ToTable();
            gvCustomers.DataSource = dt;
            gvCustomers.DataBind();
            ViewState["gv"] = gvCustomers.DataSource;
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Load Data Fail !" +"')</script>");
        }
        
        txtSearch.Text = "";
        
    }
    void addCustomer()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "InsertCustomer";

        SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
        param.Value = txtCompanyName.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
        param.Value = txtContactName.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
        param.Value = ddlContactTitle.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
        param.Value = txtAddress.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
        param.Value = txtCity.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
        if (txtRegion.Text != "")
            param.Value = txtRegion.Text;
        else
            param.Value = DBNull.Value;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
        if (txtPostalcode.Text != "")
            param.Value = txtPostalcode.Text;
        else
            param.Value = DBNull.Value;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
        param.Value = ddlCountry.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
        param.Value = txtPhone.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
        if (txtFax.Text != "")
            param.Value = txtFax.Text;
        else
            param.Value = DBNull.Value;
        cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
        con.Close();
    }
    void updateCustomer()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "UpdateCustomer";

        SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
        param.Value = txtCompanyName.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
        param.Value = txtContactName.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@contacttitle", SqlDbType.NVarChar, 30);
        param.Value = ddlContactTitle.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
        param.Value = txtAddress.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
        param.Value = txtCity.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
        if (txtRegion.Text != "")
            param.Value = txtRegion.Text;
        else
            param.Value = DBNull.Value;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
        if (txtPostalcode.Text != "")
            param.Value = txtPostalcode.Text;
        else
            param.Value = DBNull.Value;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
        param.Value = ddlCountry.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
        param.Value = txtPhone.Text;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
        if (txtFax.Text != "")
            param.Value = txtFax.Text;
        else
            param.Value = DBNull.Value;
        cmd.Parameters.Add(param);
        param = new SqlParameter("@custid", SqlDbType.Int);
        param.Value = txtcustid.Text;
        cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
        con.Close();
    }
    void deleteCustomer()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "DeleteCustomer";

        SqlParameter param = new SqlParameter("@custid", SqlDbType.Int);
        param.Value = txtcustid.Text;
        cmd.Parameters.Add(param);

        cmd.ExecuteNonQuery();
        con.Close();
    }
    void Search()
    {
        con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        da = new SqlDataAdapter("getCustomers", con);
        dt = new DataTable("Sales.Customers");
        da.Fill(dt);
        gvCustomers.AutoGenerateSelectButton = true;
        gvCustomers.AllowPaging = true;
        gvCustomers.PageSize = 10;
        DataView dv = new DataView(dt);
        if (ddlSearch.SelectedIndex == 0)
        {
            dv.RowFilter = string.Format("custid = '{0}'", txtSearch.Text);
        }
        else
            if (ddlSearch.SelectedIndex == 1)
            {
                dv.RowFilter = string.Format("companyname like '%{0}%'", txtSearch.Text);
            }
            else
                if (ddlSearch.SelectedIndex == 2)
                {
                    dv.RowFilter = string.Format("contacttitle = '{0}'", ddlSearchbyContactTitle.Text);
                }
                else
                    if (ddlSearch.SelectedIndex == 3)
                    {
                        dv.RowFilter = string.Format("country = '{0}'", ddlSearchbyCountry.Text);             
                    }
        gvCustomers.DataSource = dv;
        gvCustomers.DataBind();
        ViewState["gv"] = ((DataView)gvCustomers.DataSource).ToTable();
        
    }

    void ClearCustomer()
    {
        txtcustid.Text = "";
        txtCompanyName.Text = "";
        txtContactName.Text = "";
        ddlContactTitle.SelectedIndex = -1;
        txtAddress.Text = "";
        txtCity.Text = "";
        txtRegion.Text = "";
        txtPostalcode.Text = "";
        ddlCountry.SelectedIndex = -1;
        txtPhone.Text = "";
        txtFax.Text = "";
        txtSearch.Text= "";
    }

    protected void btnNew_Click(object sender, EventArgs e)
    {
        try
        {
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            ClearCustomer();
        }
        catch (Exception)
        {
        }
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        try
        {
            addCustomer();
            FillCustomers();
            ClearCustomer();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Add Fail !" + "')</script>");
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            updateCustomer();
            FillCustomers();
            ClearCustomer();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Update Fail !" + "')</script>");
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try
        {
            deleteCustomer();
            FillCustomers();
            ClearCustomer();
            
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Delete Fail !" +"')</script>");
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        try
        {
            ViewState["isSearch"] = true;
            Search();
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Search Fail !" + "')</script>");
        }
    }
    protected void btnShowAll_Click(object sender, EventArgs e)
    {
        FillCustomers();
        ClearCustomer();
    }
    protected void gvCustomers_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            gvCustomers.PageIndex = e.NewPageIndex;
            gvCustomers.DataSource = ViewState["gv"];
            gvCustomers.DataBind();
                          
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Page index changing failed!" + "')</script>");
        }
    }
    protected void gvCustomers_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            GridViewRow r = gvCustomers.SelectedRow;
            txtcustid.Text = r.Cells[1].Text;
            txtCompanyName.Text = Server.HtmlDecode(r.Cells[2].Text);
            txtContactName.Text = Server.HtmlDecode(r.Cells[3].Text);
            ddlContactTitle.Text = Server.HtmlDecode(r.Cells[4].Text);
            txtAddress.Text = Server.HtmlDecode(r.Cells[5].Text);
            txtCity.Text = Server.HtmlDecode(r.Cells[6].Text);
            txtRegion.Text = Server.HtmlDecode(r.Cells[7].Text);
            txtPostalcode.Text = Server.HtmlDecode(r.Cells[8].Text);
            ddlCountry.Text = Server.HtmlDecode(r.Cells[9].Text);
            txtPhone.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtFax.Text = Server.HtmlDecode(r.Cells[11].Text);
            btnAdd.Enabled = false;
            btnUpdate.Enabled = true;
            btnDelete.Enabled = true;
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Error!" + "')</script>");
        }
    }
    protected void ddlSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddlSearch.SelectedIndex == 0 || ddlSearch.SelectedIndex == 1)
        {
            ViewState["istext"] = 0;
            ddlSearchbyCountry.Visible = false;
            txtSearch.Visible = true;
            ddlSearchbyContactTitle.Visible = false;
        }
        else
            if (ddlSearch.SelectedIndex == 2)
            {
                ViewState["istext"] = 2;
                ddlSearchbyCountry.Visible = false;
                txtSearch.Visible = false;
                ddlSearchbyContactTitle.Visible = true;
            }
            else
                if (ddlSearch.SelectedIndex == 3)
                {
                    ViewState["istext"] = 3;
                    ddlSearchbyCountry.Visible = true;
                    txtSearch.Visible = false;
                    ddlSearchbyContactTitle.Visible = false;
                }
    }
    protected void gvCustomers_Sorting(object sender, GridViewSortEventArgs e)
    {
        DataView dv = new DataView((DataTable)ViewState["gv"]);
        dv.Sort = e.SortExpression + " " + (string)ViewState["Sort"];
        if ((string)ViewState["Sort"] == "ASC")
        {
            ViewState["Sort"] = "DESC";
        }
        else
        {
            ViewState["Sort"] = "ASC";
        }
        gvCustomers.DataSource = dv;
        gvCustomers.DataBind();
        ViewState["gv"] = dv.ToTable();     
    }
}
